package com.biosan.utils;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import com.biosan.DaoTnterface.JDataSet;

/**
 * User: yzq Date: 2011-9-15 Time: 15:42:29
 */
public class DButils
{

	protected java.sql.Connection conn = null;

	protected java.sql.PreparedStatement pst = null;

	protected java.sql.Statement st = null;

	protected java.sql.CallableStatement cs = null;

	protected java.sql.ResultSet rs = null;

	protected java.sql.ResultSetMetaData rm = null;

	// DatabaseConnMysql con2=new DatabaseConnMysql();con2.getConnection();
	public Connection returnConn() throws SQLException
	{

		if (conn == null)
		{
			conn = GetDataSource.getDbcpConnection();
			
			BiosanUtils.log4j("").debug(conn);
		}
		return conn;
	}

	private PreparedStatement returnPst(String sql, Object[] params)
			throws SQLException
	{

		if (conn == null || conn.isClosed())
		{			
			conn = returnConn();			
		}
		BiosanUtils.log4j("").debug("SQL >>> " + sql);
		
		pst = conn.prepareStatement(sql);
		if (params != null)
			for (int i = 0; i < params.length; i++)
				pst.setObject(i + 1, params[i]);
		return pst;

	}

	protected CallableStatement returnCs(String procName, Object[] params)
			throws SQLException
	{

		if (conn == null || conn.isClosed())
		{
			conn = returnConn();
		}
		String call = "";
		if (params != null)
		{
			call = "{call " + procName + "(";
			for (int c = 0; c < params.length - 1; c++)
				call += "?,";
			call += "?)}";
		} else
			call = "{call " + procName + "()}";
		
		BiosanUtils.log4j("").debug("SQL >>> " + call);
		cs = conn.prepareCall(call);
		if (params != null)
			for (int i = 0; i < params.length; i++)
				cs.setObject(i + 1, params[i]);
		return cs;

	}

	public void clearAllsql()
	{
		try
		{
			
			if (rs != null)
				rs.close();
			if (cs != null)
				cs.close();
			if (st != null)
				st.close();
			if (pst != null)
				pst.close();
			if (conn != null)
			{
				// =======================这里可以有其他方式；================
				
				conn.close();
				//conn=null;
				//System.out.println(conn+"-------连接关闭------");
				// con2.getConnection().close();
				// =========================================================
			}
			rs = null;
			cs = null;
			st = null;
			pst = null;
		} catch (SQLException ex)
		{
			ex.printStackTrace();
		}
	}

	public boolean execute(String sql, Object[] params, boolean only)
	{

		boolean bVal = false;
		try
		{
			if (only)
			{
				rs = returnPst(sql, params).executeQuery();
				while (rs.next())
					bVal = true;
			} else
			{
				returnPst(sql, params).executeUpdate();
				bVal = true;
			}
		} catch (SQLException ex)
		{
			ex.printStackTrace();
		} finally
		{
			clearAllsql();
		}
		return bVal;
	}

	public boolean executeBatch(String[] sqlBatch)
	{

		boolean bVal = false;
		try
		{
			conn = returnConn();
			st = conn.createStatement();
			boolean autoCommit = conn.getAutoCommit();

			for (int i = 0; i < sqlBatch.length; i++)
			{
				if (sqlBatch[i] != null && !sqlBatch[i].equals(""))
					st.addBatch(sqlBatch[i] + ";");
				System.out.println("SQL >>> " + sqlBatch[i]);
			}
			conn.setAutoCommit(false);
			st.executeBatch();
			conn.commit();
			conn.setAutoCommit(autoCommit);
			bVal = true;
		} catch (SQLException ex)
		{
			try
			{
				conn.rollback();
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
			ex.printStackTrace();
		} finally
		{
			clearAllsql();
		}
		return bVal;
	}

	public boolean executeCall(String procName, Object[] params)
	{

		boolean bVal = false;
		try
		{
			returnCs(procName, params).executeUpdate();
			bVal = true;
		} catch (Exception ex)
		{
			ex.printStackTrace();
		} finally
		{
			clearAllsql();
		}
		return bVal;
	}

	private ResultSet returnResultSet(String sql, Object[] params,
			boolean executeCall)
	{
		try
		{
			if (executeCall)
				rs = returnCs(sql, params).executeQuery();
			else
				rs = returnPst(sql, params).executeQuery();
			
		} catch (Exception ex)
		{
			ex.printStackTrace();
		}
		
		return rs;
	}


	public String changeFont(ResultSet rs, String column) throws SQLException
	{
		return rs.getString(column) == null ? "" : rs.getString(column);
	}

	public int returnColumnCount(ResultSet rs) throws SQLException
	{
		return rs.getMetaData().getColumnCount();
	}

	public static String getSqlStartEnd(String columnName, String tableName,
			int currentPage, int pageSize, String orderBy, String andwhere)
	{
		int start = 1;
		if (currentPage > 1)
			start = (currentPage - 1) * pageSize + 1;
		int end = start + pageSize - 1;
		String sql = "";
		String strwhere = "";
		if (andwhere != null)
		{

			strwhere = " " + andwhere;

		}

		sql = "select " + columnName + " from ( select rownum r,a.* from "
				+ tableName + " a where rownum<=" + end + strwhere
				+ " ) b where b.r>=" + start;

		if (start == -1 && end == -1)

		{
			sql = "select " + columnName + " from " + tableName;
		}

		if (orderBy != null)

		{
			sql = sql + " order by " + orderBy;
		}

		return sql;

	}

	public int getRowCount(String sql) throws SQLException
	{

		String sqlDo = "";
		if (sql.indexOf("@#") != -1)
		{
			String[] sqlArr = sql.split("@#");
			sqlDo = sqlArr[1];
		} else
			sqlDo = sql;

		int count = 0;
		try
		{
			rs = this.returnResultSet("select count(*) " + sqlDo, null, false);
			while (rs.next())
				count = rs.getInt(1);
		} catch (Exception ce)
		{
			ce.printStackTrace();
		} finally
		{
			System.out.println("conn5:"+conn);
			clearAllsql();
		}
		return count;
	}

	public JDataSet getJDataSetAsPager(String columnName, String tableName,
			int currentPage, int pageSize, String orderBy, String andwhere)

	{
		String strVal = getSqlStartEnd(columnName, tableName, currentPage,
				pageSize, orderBy, andwhere);
		return getJDataSet(strVal, null, true, false);
	}

//	public ResultSet getResultSetAsPager(String columnName, String tableName,
//			int currentPage, int pageSize, String orderBy, String andwhere)
//			throws SQLException
//
//	{
//
//		String strVal = getSqlStartEnd(columnName, tableName, currentPage,
//				pageSize, orderBy, andwhere);
//		return returnResultSet(strVal, null, false);
//
//	}

	public JSONArray getJSONArrayAsPager(String columnName, String tableName,
			int currentPage, int pageSize, String orderBy, String andwhere)
			throws SQLException
	{
		try
		{
			String strVal = getSqlStartEnd(columnName, tableName, currentPage,
					pageSize, orderBy, andwhere);			
			return getSqlToJson(strVal, null, false);
		} finally
		{
			clearAllsql();
		}

	}

	public JSONArray getSqlToJson(String sql, Object[] params,boolean execute) throws SQLException
	{
		try
		{
			rs = this.returnResultSet(sql, params, execute);
			ResultSetMetaData md = rs.getMetaData();
			int num = md.getColumnCount();
			JSONArray array = new JSONArray();
			while (rs.next())
			{
				JSONObject mapOfColValues = new JSONObject();

				for (int i = 1; i <= num; i++)
				{
					mapOfColValues.put(md.getColumnName(i), rs.getObject(i));
				}
				array.add(mapOfColValues);
			}
			return array;
		} finally
		{
			//System.out.println("conn4 没有关闭 " +conn);
			clearAllsql();
		}
	}

	public JDataSet getJDataSet(String sql, Object[] params,
			boolean fillColumnNames, boolean executeCall)
	{

		JDataSet jds = null;
		try
		{
			if (executeCall)
				rs = returnCs(sql, params).executeQuery();
			else
				rs = returnPst(sql, params).executeQuery();
			jds = loadJDataSet(rs, fillColumnNames);
		} catch (Exception ex)
		{
			ex.printStackTrace();
		} finally
		{
			clearAllsql();
		}
		return jds;
	}

	public JDataSet loadJDataSet(ResultSet rs, boolean fillColumnNames)
	{

		JDataSet jds = new JDataSet();
		try
		{
			int columnCount = returnColumnCount(rs);
			if (fillColumnNames)
			{
				String[] columnNames = new String[columnCount];
				String[] columnTypeNames = new String[columnCount];
				for (int i = 0; i < columnCount; i++)
				{
					columnNames[i] = rs.getMetaData().getColumnName(i + 1);
					columnTypeNames[i] = rs.getMetaData().getColumnTypeName(
							i + 1);
				}
				jds.setColumnNames(columnNames);
				jds.setColumnTypeNames(columnTypeNames);
			}
			while (rs.next())
			{
				String[] row = new String[columnCount];
				for (int i = 0; i < columnCount; i++)
					row[i] = rs.getString(i + 1) == null ? "" : rs.getString(
							i + 1).trim();
				jds.addRow(row);
			}
		} catch (Exception ex)
		{
			ex.printStackTrace();
		}
		return jds;
	}
	
	/**
     * 获得当前日期和时间
     *
     * @return String 当前日期和时间，格式：yyyy-MM-dd HH:mm:ss
     */
    public static String getCurDateTime()
    {
        SimpleDateFormat nowDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        return nowDate.format(new Date());

    }

}
